package merlin.dao;

import java.sql.*;
import java.text.*;
import java.util.*;

import merlin.beans.*;

import merlin.utils.*;

public class IndustryWatchDAO{

	Connection con;

	public IndustryWatchDAO(Connection connection){
		con = connection;
	}

	public IWCustomerDetailsBean getIWCustomerDetails(String emailAddress)throws SQLException{
		IWCustomerDetailsBean bean = null;

		String sql = "SELECT COALESCE(IW_SUBSCRIPTION_TYPE,'A'),IW_PDF_CREDITS FROM IW_CUSTOMER_DETAILS WHERE IW_EMAIL_ADDRESS = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,emailAddress);

		ResultSet rs = pstmt.executeQuery();

		if(rs.next()){
			bean = new IWCustomerDetailsBean();
			bean.setSubscriptionType(rs.getString(1));
			bean.setPdfCredits(rs.getInt(2));
		}

		rs.close();
		pstmt.close();
		return bean;
	}

	public int addIWCustomerDetails(IWCustomerDetailsBean bean)throws SQLException{

		int newID = generateNewId();

		String sql = "INSERT INTO IW_CUSTOMER_DETAILS" +
					 "(IW_CUSTOMER_DETAILS_ID,IW_EMAIL_ADDRESS,IW_SUBSCRIPTION_END_DATE,IW_WEBSITE_ORDERS_ID,IW_COMPANY_NAME,IW_PASSWORD,IW_FIRST_NAME,IW_SURNAME,IW_TELEPHONE_NUMBER,IW_SUBSCRIPTION_TYPE,IW_PDF_CREDITS)" +
					 "VALUES(?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement pstmt = con.prepareStatement(sql);

		pstmt.setInt(1,newID);
		pstmt.setString(2,bean.getEmailAddress().toLowerCase());
		pstmt.setDate(3,new java.sql.Date(bean.getSubscriptionEndDate().getTime()));
		pstmt.setInt(4,bean.getWebsiteOrdersId());
		pstmt.setString(5,bean.getCompanyName());
		// default password is "iw" + <orderId>. The total string shouldn't be bigger than 12 chars.
		String password = "iw" + bean.getWebsiteOrdersId();
		if(password.length() > 12){
			password = password.substring(0,12);
		}
		pstmt.setString(6,password);
		pstmt.setString(7,bean.getFirstName());
		pstmt.setString(8,bean.getSurname());
		pstmt.setString(9,bean.getTelephoneNumber());
		pstmt.setString(10,bean.getSubscriptionType());
		// No PDFs for types A and C
		if("A".equals(bean.getSubscriptionType()) || "C".equals(bean.getSubscriptionType()))
			pstmt.setInt(11,0);
		else if("B".equals(bean.getSubscriptionType()))
			pstmt.setInt(11,10);

    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();
    	return updateCols;
	}

	public int updateIWCustomerDetails(IWCustomerDetailsBean bean)throws SQLException{
		String sql = "UPDATE IW_CUSTOMER_DETAILS SET IW_SUBSCRIPTION_END_DATE = ?,IW_WEBSITE_ORDERS_ID = ?,IW_PASSWORD = ?,IW_TRIAL_INDICATOR = NULL,IW_SUBSCRIPTION_TYPE = ?,IW_PDF_CREDITS = COALESCE(IW_PDF_CREDITS,0) + ? WHERE IW_EMAIL_ADDRESS = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setDate(1,new java.sql.Date(bean.getSubscriptionEndDate().getTime()));
		pstmt.setInt(2,bean.getWebsiteOrdersId());
		// default password is "iw" + <orderId>. The total string shouldn't be bigger than 12 chars.
		String password = "iw" + bean.getWebsiteOrdersId();
		if(password.length() > 12){
			password = password.substring(0,12);
		}
		pstmt.setString(3,password);
        pstmt.setString(4,bean.getSubscriptionType());

		// No PDFs for types A & C
		if("A".equals(bean.getSubscriptionType()) || "C".equals(bean.getSubscriptionType()))
			pstmt.setInt(5,0);
		else if("B".equals(bean.getSubscriptionType()))
			pstmt.setInt(5,10);
		pstmt.setString(6,bean.getEmailAddress());
    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();
    	return updateCols;
	}

	public int updateIWCustomerLicenceAcceptance(String emailAddress)throws SQLException{
		String sql = "UPDATE IW_CUSTOMER_DETAILS SET IW_LICENCE_ACCEPTANCE_DATE = CURRENT_DATE WHERE IW_EMAIL_ADDRESS = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,emailAddress);
    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();
    	return updateCols;
	}

	public int updatePdfCreditsPurchase(String emailAddress, int pdfIncrement,int orderId)throws SQLException{
		String sql = "UPDATE IW_CUSTOMER_DETAILS SET IW_PDF_CREDITS = COALESCE(IW_PDF_CREDITS) + ?,IW_WEBSITE_ORDERS_ID = ? WHERE IW_EMAIL_ADDRESS = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,pdfIncrement);
		pstmt.setInt(2,orderId);
		pstmt.setString(3,emailAddress);
    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();
    	return updateCols;
	}

	public int decrementPdfCredits(String emailAddress, int pdfDecrement)throws SQLException{
		String sql = "UPDATE IW_CUSTOMER_DETAILS SET IW_PDF_CREDITS = COALESCE(IW_PDF_CREDITS) - ? WHERE IW_EMAIL_ADDRESS = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,pdfDecrement);
		pstmt.setString(2,emailAddress);
    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();
    	return updateCols;
	}

	public int deleteByWebsiteOrderId(int orderId)throws SQLException{
		String sql = "DELETE FROM IW_CUSTOMER_DETAILS WHERE IW_WEBSITE_ORDERS_ID = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,orderId);
    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();
    	return updateCols;
	}

	public IWCustomerDetailsBean loginForIndustryWatch(String emailId,String password)throws SQLException{

		String sql = "SELECT IW_CUSTOMER_DETAILS_ID, IW_EMAIL_ADDRESS, IW_SUBSCRIPTION_END_DATE, IW_WEBSITE_ORDERS_ID,IW_LICENCE_ACCEPTANCE_DATE,IW_COMPANY_NAME,IW_TRIAL_INDICATOR,IW_FIRST_NAME,IW_SURNAME,IW_TELEPHONE_NUMBER,IW_SUBSCRIPTION_TYPE,COALESCE(IW_VISITS,0) as IW_VISITS " +
					 " FROM IW_CUSTOMER_DETAILS WHERE IW_EMAIL_ADDRESS = ? AND IW_PASSWORD = ? ";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,emailId);
		pstmt.setString(2,password);
		ResultSet rs = pstmt.executeQuery();

		IWCustomerDetailsBean bean = null;
		if(rs.next()){
			bean = new IWCustomerDetailsBean();
			bean.setCustomerDetailsId(rs.getInt("IW_CUSTOMER_DETAILS_ID"));
			bean.setEmailAddress(rs.getString("IW_EMAIL_ADDRESS"));
			bean.setSubscriptionEndDate(rs.getDate("IW_SUBSCRIPTION_END_DATE"));
			bean.setWebsiteOrdersId(rs.getInt("IW_WEBSITE_ORDERS_ID"));
			bean.setLicenceAcceptanceDate(rs.getDate("IW_LICENCE_ACCEPTANCE_DATE"));
			bean.setCompanyName(rs.getString("IW_COMPANY_NAME"));
			bean.setTrialIndicator(rs.getBoolean("IW_TRIAL_INDICATOR"));
			bean.setFirstName(rs.getString("IW_FIRST_NAME"));
			bean.setSurname(rs.getString("IW_SURNAME"));
			bean.setTelephoneNumber(rs.getString("IW_TELEPHONE_NUMBER"));
			bean.setSubscriptionType(rs.getString("IW_SUBSCRIPTION_TYPE"));
			bean.setVisits(rs.getInt("IW_VISITS"));
		}
		rs.close();
		pstmt.close();

		if(bean != null){
			// get the number of the PDF downloads done by the user.
			pstmt = con.prepareStatement("SELECT COUNT(*) FROM IW_REPORT_DOWNLOAD_HISTORY WHERE CUSTOMER_ID = ?");
			pstmt.setInt(1,bean.getCustomerDetailsId());
			rs = pstmt.executeQuery();

			if(rs.next()){
				bean.setNumPDFReportsDownloadHistory(rs.getInt(1));
			}

			rs.close();
			pstmt.close();

			// increment the number of times this user has visited.
			bean.setVisits(bean.getVisits() + 1);
			pstmt = con.prepareStatement("UPDATE IW_CUSTOMER_DETAILS SET IW_VISITS = ? WHERE IW_EMAIL_ADDRESS = ?");
			pstmt.setInt(1, bean.getVisits());
			pstmt.setString(2, emailId);
			pstmt.executeUpdate();
			pstmt.close();
		}



		return bean;
	}

	public int changePassword(int customerId,String oldPassword,String newPassword)throws SQLException{
		System.out.println(customerId);
		System.out.println(oldPassword);
		System.out.println(newPassword);
		String sql = "UPDATE IW_CUSTOMER_DETAILS SET IW_PASSWORD = ? WHERE IW_CUSTOMER_DETAILS_ID = ? AND IW_PASSWORD = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,newPassword);
		pstmt.setInt(2,customerId);
		pstmt.setString(3,oldPassword);
    	int updateCols = pstmt.executeUpdate();
    	System.out.println(updateCols);
    	pstmt.close();
    	return updateCols;

	}

	public int registerTrialUser(String firstName,String surname,String companyName,String eMailAddress,String telephone)throws SQLException{
		int newID = generateNewId();
		// Give 3 PDF download credits to a trial user.
		String sql = "INSERT INTO IW_CUSTOMER_DETAILS(IW_CUSTOMER_DETAILS_ID,IW_FIRST_NAME,IW_SURNAME,IW_COMPANY_NAME,IW_EMAIL_ADDRESS,IW_TELEPHONE_NUMBER,IW_TRIAL_INDICATOR,IW_PASSWORD,IW_PDF_CREDITS)VALUES(?,?,?,?,?,?,TRUE,?,0)";

		PreparedStatement pstmt = con.prepareStatement(sql);
  		pstmt.setInt(1,newID);
  		pstmt.setString(2,firstName);
  		pstmt.setString(3,surname);
  		pstmt.setString(4,companyName);
  		pstmt.setString(5,eMailAddress.toLowerCase());
  		pstmt.setString(6,telephone);
  		pstmt.setString(7,"password");

    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();
    	return (updateCols > 0 ? newID : updateCols);
	}

	public List getIndustryWatchData(String industrySegments,String[] financialMeasures, String[] dataSeriesCols,String orderByClause)throws SQLException{

		DecimalFormat dFormat = new DecimalFormat("###,###.00");

		int maxRecords = Integer.parseInt(ApplicationResources.getInstance().getProperty("maxIWRecords"));
		int recordCounter = 0;
		boolean withinRecordLimit = true;

		String sql = buildQuery(industrySegments,financialMeasures,dataSeriesCols,orderByClause,true);
		System.out.println("\n" + new java.util.Date() + " : IW Query \n" + sql + "\n\n");

		List lstIndustries = new ArrayList();
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next() && withinRecordLimit){
			IndustrySegmentBean indBean = new IndustrySegmentBean();
			int colNumber = 1;
			indBean.setIndustryId(rs.getInt(colNumber++));			// IAD_REPORT_NUMBER
			indBean.setIndustryName(rs.getString(colNumber++)); 	// REPORT_TITLE
			indBean.setYearOfFirstAverage(rs.getInt(colNumber++));	// IAD_YEAR_OF_FIRST_AVERAGE
			for(int i=0;i<financialMeasures.length  && withinRecordLimit;i++){
				FinancialMeasureBean fmBean = new FinancialMeasureBean();
				fmBean.setMeasureName(financialMeasures[i]);
				for(int j=0;j<dataSeriesCols.length && withinRecordLimit;j++){
					DataSeriesBean dsBean = new DataSeriesBean();
					dsBean.setSeriesName(dataSeriesCols[j]);
					dsBean.setValueYear1(dFormat.format(rs.getDouble(colNumber++)));
					dsBean.setValueYear2(dFormat.format(rs.getDouble(colNumber++)));
					dsBean.setValueYear3(dFormat.format(rs.getDouble(colNumber++)));
					dsBean.setValueYear4(dFormat.format(rs.getDouble(colNumber++)));
					dsBean.setValueYear5(dFormat.format(rs.getDouble(colNumber++)));
					dsBean.setValueYear6(dFormat.format(rs.getDouble(colNumber++)));

					fmBean.addDataSeries(dsBean);
					recordCounter++;
					// when the number of records exceed the maxRecords, break out.
					if(recordCounter == maxRecords){
						withinRecordLimit = false;
					}
				}
				indBean.addFinancialMeasure(fmBean);
			}
			lstIndustries.add(indBean);
		}

		rs.close();
		stmt.close();


		return lstIndustries;
	}


	public List getIndustryWatchAllSectorsData(String industrySegments,String[] financialMeasures, String[] dataSeriesCols,String orderByClause)throws SQLException{

		DecimalFormat dFormat = new DecimalFormat("###,###.00");

		String sql = buildQueryForAllSectors(industrySegments,financialMeasures,dataSeriesCols,orderByClause,true);
		System.out.println("\n" + new java.util.Date() + " : IW All Sectors Query \n" + sql + "\n\n");

		List lstIndustries = new ArrayList();
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		int currentYear = Calendar.getInstance().get(Calendar.YEAR);
		while(rs.next()){
			IndustrySegmentBean indBean = new IndustrySegmentBean();
			int colNumber = 1;
			indBean.setIndustryId(rs.getInt(colNumber++));			// IAD_REPORT_NUMBER
			indBean.setIndustryName(rs.getString(colNumber++)); 	// REPORT_TITLE
			indBean.setYearOfFirstAverage(currentYear);
			for(int i=0;i<financialMeasures.length;i++){
				FinancialMeasureBean fmBean = new FinancialMeasureBean();
				fmBean.setMeasureName(financialMeasures[i]);
				for(int j=0;j<dataSeriesCols.length;j++){
					DataSeriesBean dsBean = new DataSeriesBean();
					dsBean.setSeriesName(dataSeriesCols[j]);
					dsBean.setValueYear1(dFormat.format(rs.getDouble(colNumber++)));

					fmBean.addDataSeries(dsBean);
				}
				indBean.addFinancialMeasure(fmBean);
			}
			lstIndustries.add(indBean);
		}

		rs.close();
		stmt.close();


		return lstIndustries;
	}

	public IndustryWatchSummaryBean[] getIndustryWatchSummaryData(String industrySegments,String[] financialMeasures, String[] dataSeriesCols,String orderByClause)throws SQLException{
		List lstSummaryData = new LinkedList();

		String sql = buildQuery(industrySegments,financialMeasures,dataSeriesCols,orderByClause,false);
		System.out.println("\n" + new java.util.Date() + " : IW Summary Query \n" + sql + "\n\n");

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			IndustryWatchSummaryBean iwSummaryBean = new IndustryWatchSummaryBean();
			int colNumber = 1;
			iwSummaryBean.setIndustryId(rs.getInt(colNumber++));			// IAD_REPORT_NUMBER
			iwSummaryBean.setIndustryName(rs.getString(colNumber++)); 		// REPORT_TITLE
			iwSummaryBean.setYearOfFirstAverage(rs.getInt(colNumber++));    // IAD_YEAR_OF_FIRST_AVERAGE

			// create a double array to hold all the IW data.
			double[] arrData = new double[financialMeasures.length * dataSeriesCols.length * 5];
			int counter = 0;
			for(int i=0;i<financialMeasures.length;i++){
				for(int j=0;j<dataSeriesCols.length;j++){
					for(int k=0;k<5;k++){
						arrData[counter++] = rs.getDouble(colNumber++);
					}
				}
			}

			iwSummaryBean.setIndustryData(arrData);
			lstSummaryData.add(iwSummaryBean);
		}

		IndustryWatchSummaryBean[] arrSummaryBeans = (IndustryWatchSummaryBean[])lstSummaryData.toArray(new IndustryWatchSummaryBean[0]);
		return arrSummaryBeans;
	}



	/**
	 * Utility function to construct the query that is used to access the Industry Watch table.
	 *
	 * @param industrySegments
	 * @param financialMeasures
	 * @param dataSeriesCols
	 * @param orderByClause
	 * @return
	 */
	private String buildQuery(String industrySegments,String[] financialMeasures, String[] dataSeriesCols,String orderByClause,boolean buildOrderByClause){
		StringBuffer sbSql = new StringBuffer("SELECT IAD_REPORT_NUMBER,REPORT_TITLE,IAD_YEAR_OF_FIRST_AVERAGE");
		for(int i=0;i<financialMeasures.length;i++){
			for(int j=0;j<dataSeriesCols.length;j++){
				for(int k=6;k>=1;k--){
					sbSql.append(',').append(financialMeasures[i]).append(dataSeriesCols[j]).append(k);
				}
			}
		}
		sbSql.append(" FROM INDUSTRY_AVERAGE_DATA,REPORT_TITLES ");
		sbSql.append(" WHERE IAD_REPORT_NUMBER IN(").append(industrySegments).append(") ");
		sbSql.append(" AND REPORT_TITLES_ID = IAD_REPORT_NUMBER ");

		// create the order by clause by listing the measures in descending order
		if(buildOrderByClause){
			StringBuffer sbOrderBy = new StringBuffer();
			for(int i=0;i<financialMeasures.length;i++){
				for(int j=0;j<dataSeriesCols.length;j++){
					for(int k=6;k>=1;k--){
						sbOrderBy.append(',').append(financialMeasures[i]).append(dataSeriesCols[j]).append(k).append(" DESC");
					}
				}
			}
			sbSql.append("ORDER BY ").append(sbOrderBy.substring(1));
		}else{
			if(orderByClause != null){
				sbSql.append("ORDER BY ").append(orderByClause);
			}
		}

		return sbSql.toString();
	}

	private String buildQueryForAllSectors(String industrySegments,String[] financialMeasures, String[] dataSeriesCols,String orderByClause,boolean buildOrderByClause){
		StringBuffer sbSql = new StringBuffer("SELECT IPD_REPORT_NUMBER,REPORT_TITLE");
		for(int i=0;i<financialMeasures.length;i++){
			for(int j=0;j<dataSeriesCols.length;j++){
					sbSql.append(',').append(financialMeasures[i]).append(dataSeriesCols[j]).append(1);
			}
		}
		sbSql.append(" FROM INDUSTRY_PROFILE_DATA,REPORT_TITLES ");
		sbSql.append(" WHERE IPD_REPORT_NUMBER IN(").append(industrySegments).append(") ");
		sbSql.append(" AND REPORT_TITLES_ID = IPD_REPORT_NUMBER ");

		// create the order by clause by listing the measures in descending order
		if(buildOrderByClause){
			StringBuffer sbOrderBy = new StringBuffer();
			for(int i=0;i<financialMeasures.length;i++){
				for(int j=0;j<dataSeriesCols.length;j++){
					sbOrderBy.append(',').append(financialMeasures[i]).append(dataSeriesCols[j]).append(1).append(" DESC");
				}
			}
			sbSql.append("ORDER BY ").append(sbOrderBy.substring(1));
		}else{
			if(orderByClause != null){
				sbSql.append("ORDER BY ").append(orderByClause);
			}
		}

		return sbSql.toString().replaceAll("IAD_","IPD_");
	}

	public int generateNewId()throws SQLException{
		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT MAX(COALESCE(IW_CUSTOMER_DETAILS_ID,0)) + 1 FROM NSD.IW_CUSTOMER_DETAILS");
		rs.next();
		int newID = rs.getInt(1);
		System.out.println(newID);
		rs.close();
		stmt.close();

		return newID;
	}

	public int generateNewDownloadHistoryId()throws SQLException{
		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT MAX(COALESCE(REPORT_DOWNLOAD_HISTORY_ID,0)) + 1 FROM NSD.IW_REPORT_DOWNLOAD_HISTORY");
		rs.next();
		int newID = rs.getInt(1);
		System.out.println(newID);
		rs.close();
		stmt.close();

		return newID;
	}

	public String[] addReportDownloadHistory(int customerId,int[] reportIds)throws SQLException{
		String[] arrUUIDs = new String[reportIds.length];

		PreparedStatement pstmt = con.prepareStatement("INSERT INTO IW_REPORT_DOWNLOAD_HISTORY(REPORT_DOWNLOAD_HISTORY_ID,REPORT_ID,CUSTOMER_ID,DOWNLOAD_UUID) VALUES(?,?,?,?)");
		for(int i = 0;i < reportIds.length;i++){
			pstmt.setInt(1,generateNewDownloadHistoryId());
			pstmt.setInt(2,reportIds[i]);
			pstmt.setInt(3,customerId);
			arrUUIDs[i] = UUID.randomUUID().toString();
			pstmt.setString(4,arrUUIDs[i]);
			pstmt.executeUpdate();
			pstmt.clearParameters();
		}
		pstmt.close();
		return arrUUIDs;
	}

	public int updatePDFDownloadDate(String uuid)throws SQLException{
		int updateCount = -1;

		PreparedStatement pstmt = con.prepareStatement("UPDATE IW_REPORT_DOWNLOAD_HISTORY SET DOWNLOAD_DATE = CURRENT_DATE WHERE DOWNLOAD_UUID = ?");
		pstmt.setString(1,uuid);
		updateCount = pstmt.executeUpdate();
		pstmt.close();

		return updateCount;
	}

	public int getReportIdByUUID(String uuid)throws SQLException{
		int reportId = -1;

  		PreparedStatement pstmt = con.prepareStatement("SELECT REPORT_ID FROM IW_REPORT_DOWNLOAD_HISTORY WHERE DOWNLOAD_UUID = ?");
  		pstmt.setString(1,uuid);
  		ResultSet rs = pstmt.executeQuery();

  		if(rs.next()){
  			reportId = rs.getInt(1);
  		}

  		rs.close();
  		pstmt.close();

  		return reportId;
	}

	/**
	 * Gets the actual PDF data of the report.
	 * 
	 * There are two types of reports available - normal, and branded. 
	 * The 'brandedReport' parameter indicates what type of report is to be fetched. (currently branded reports are available only for BIB service).
	 * 
	 * @param reportId
	 * @param brandedReport
	 * @param reportFormat can be PDF, XLS or Ranking (constants defined in MerlinUtilities class)
	 * @return
	 * @throws SQLException
	 */
	public PDFReportFileBean getPDFReportByReportId(int reportId, String reportFileColumnName, int reportFormat)throws SQLException{
		
		String dataColumn = null;
		
		switch(reportFormat){
		case MerlinUtilities.REPORT_TYPE_XLS :
			dataColumn = "REPORT_DATA";
			break;
		case MerlinUtilities.REPORT_TYPE_RANKINGS :
			dataColumn = "REPORT_RANKINGS";
			break;
		default:	// PDF - whether the branded report is retrieved or not is dependent on the value of the reportFileColumnName value.
			dataColumn = reportFileColumnName;
		}
		String sql = "SELECT " + dataColumn + ",REPORT_TITLE,REPORT_ZIPPED FROM REPORT_TITLES A,REPORT_FILES B WHERE A.REPORT_TITLES_ID = ? AND A.REPORT_TITLES_ID = B.REPORT_ID";
  		PreparedStatement pstmt = con.prepareStatement(sql);
  		System.out.println(sql);
  		pstmt.setInt(1,reportId);

  		ResultSet rs = pstmt.executeQuery();
  		PDFReportFileBean pdfData = new PDFReportFileBean();

  		if(rs.next()){
  			Blob blob = rs.getBlob(1);
  			if(blob != null){
  				int length = (int)blob.length();
  				pdfData.setFileData(blob.getBytes(1,length));
  				pdfData.setFileName(rs.getString(2));
  				pdfData.setReportZipped(rs.getInt(3));
  			}
  		}

  		rs.close();
  		pstmt.close();

  		return pdfData;
	}

	
	public PDFDownloadDetailsBean[] getPDFDownloadBeansByCustomerId(int customerId)throws SQLException{

    	ArrayList list = new ArrayList();
    	

  		PreparedStatement pstmt = con.prepareStatement("SELECT A.REPORT_ID, REPORT_TITLE, DOWNLOAD_UUID, DOWNLOAD_DATE, DOWNLOAD_DATE AS EDITION FROM IW_REPORT_DOWNLOAD_HISTORY A,REPORT_TITLES B WHERE CUSTOMER_ID = ? AND A.REPORT_ID = B.REPORT_TITLES_ID");
  		pstmt.setInt(1,customerId);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next()){
			PDFDownloadDetailsBean bean = new PDFDownloadDetailsBean();
			bean.setReportId(rs.getInt(1));
			bean.setReportTitle(rs.getString(2));
			bean.setDownloadUuid(rs.getString(3));
			bean.setDownloadDate(rs.getDate(4));
			/*
			if(rs.getDate(5)!= null){
				bean.setReportEdition(fmt.format(rs.getDate(5)));
			}else{
				bean.setReportEdition("");
			}
			*/
			list.add(bean);
		}

		rs.close();
		pstmt.close();

		PDFDownloadDetailsBean[] arrBeans = (PDFDownloadDetailsBean[])list.toArray(new PDFDownloadDetailsBean[0]);
		return arrBeans;
	}

	/**
	 * There are two types of reports available - normal, and branded. 
	 * The 'brandedReport' parameter indicates what type of report is to be fetched. (currently branded reports are available only for BIB service).
	 *
	 * @param arrBeans
	 * @param brandedReport
	 * @return
	 * @throws SQLException
	 */
	public PDFDownloadDetailsBean[] getPDFDownloadDetailsByReportIds(PDFDownloadDetailsBean[] arrBeans, boolean brandedReport, int reportFormat)throws SQLException{

		String reportIds = "";
		String reportFileColumnName = null;
		
		SimpleDateFormat fmt = new SimpleDateFormat("MMMMM yyyy");
		
		switch(reportFormat){
		case MerlinUtilities.REPORT_TYPE_XLS :
			reportFileColumnName = "REPORT_DATA";
			break;
		case MerlinUtilities.REPORT_TYPE_RANKINGS :
			reportFileColumnName = "REPORT_RANKINGS";
			break;
		default:	// PDF
			reportFileColumnName = (brandedReport ? ApplicationResources.getInstance().getProperty("bibBrandedReportColumnName", "REPORT_FILE") : "REPORT_FILE");
			break;
		}
		

		for(int i=0;i<arrBeans.length;i++){
			reportIds += "," + arrBeans[i].getReportId();
		}

		reportIds = reportIds.substring(1);

		String sql = "SELECT REPORT_ID,OCTET_LENGTH(" + reportFileColumnName + "), REPORT_DATE FROM REPORT_FILES WHERE REPORT_ID IN(" + reportIds + ")";

		System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next()){
			int reportId = rs.getInt(1);
			long reportSize = rs.getInt(2);

			for(int i=0;i<arrBeans.length;i++){
				if(arrBeans[i].getReportId() == reportId){
					if(reportSize > 0){
						arrBeans[i].setStatus(MerlinUtilities.REPORT_STATUS_AVAILABLE);
						arrBeans[i].setReportSize(reportSize);						
					}
					
					if(rs.getDate(3)!= null){
						arrBeans[i].setReportEdition(fmt.format(rs.getDate(3)));
					}else{
						arrBeans[i].setReportEdition("");
					}
				}
			}
		}

		rs.close();
		pstmt.close();

		return arrBeans;
	}

	///////////////////////////// Functions related to Merlin E-Book downloads /////////////////////////

	/**
	 * Generate a new id from the MSAL_REPORT_DOWNLOAD_HISTORY
	 */
	public int generateNewMerlinEbookDownloadHistoryId()throws SQLException{
		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT MAX(COALESCE(REPORT_DOWNLOAD_HISTORY_ID,0)) + 1 FROM NSD.MSAL_REPORT_DOWNLOAD_HISTORY");
		rs.next();
		int newID = rs.getInt(1);
		System.out.println(newID);
		rs.close();
		stmt.close();

		return newID;
	}

	/**
	 * Function to add download history for Merlin e-books entries.
	 * This is different from the IW PDF downloads.
	 */
	public String[] addMerlinEBookDownloadHistory(int orderId,int[] reportIds)throws SQLException{
		String[] arrUUIDs = new String[reportIds.length];

		PreparedStatement pstmt = con.prepareStatement("INSERT INTO MSAL_REPORT_DOWNLOAD_HISTORY(REPORT_DOWNLOAD_HISTORY_ID,REPORT_ID,ORDER_ID,DOWNLOAD_UUID) VALUES(?,?,?,?)");
		for(int i = 0;i < reportIds.length;i++){
			pstmt.setInt(1,generateNewMerlinEbookDownloadHistoryId());
			pstmt.setInt(2,reportIds[i]);
			pstmt.setInt(3,orderId);
			arrUUIDs[i] = UUID.randomUUID().toString();
			pstmt.setString(4,arrUUIDs[i]);
			pstmt.executeUpdate();
			pstmt.clearParameters();
		}
		pstmt.close();
		return arrUUIDs;
	}

	public int updateMerlinEbookDownloadDate(String uuid)throws SQLException{
		int updateCount = -1;

		PreparedStatement pstmt = con.prepareStatement("UPDATE MSAL_REPORT_DOWNLOAD_HISTORY SET DOWNLOAD_DATE = CURRENT_DATE WHERE DOWNLOAD_UUID = ?");
		pstmt.setString(1,uuid);
		updateCount = pstmt.executeUpdate();
		pstmt.close();

		return updateCount;
	}

	public PDFDownloadDetailsBean getMerlinEbookReportIdByUUID(String uuid)throws SQLException{
		int reportId = -1;
		String reportFieldName = null;
		
  		PreparedStatement pstmt = con.prepareStatement("SELECT REPORT_ID, ORDER_ID FROM MSAL_REPORT_DOWNLOAD_HISTORY WHERE DOWNLOAD_UUID = ?");
  		pstmt.setString(1,uuid);
  		ResultSet rs = pstmt.executeQuery();
  		int orderId = -1;
  		if(rs.next()){
  			reportId = rs.getInt(1);
  			orderId = rs.getInt(2);
  		}

  		rs.close();
  		pstmt.close();
  		
  		if(orderId > -1){
  			// determine if the download request was generated by a BIB service user or not.
  			pstmt = con.prepareStatement("SELECT WO_CUSTOMER_DETAILS_ID FROM WEBSITE_ORDERS WHERE WEBSITE_ORDERS_ID = ?");
  			pstmt.setInt(1, orderId);
  			rs = pstmt.executeQuery();
  			int customerId = 0;
  			if(rs.next()){
  				customerId = rs.getInt(1); // if the field is null in DB, it will return zero.
  			}
  			
  			rs.close();
  			pstmt.close();
  			
  			if(customerId == 0){
  				// no customer id, that means it was a MSAL report download request, not a BIB service download request.
  				reportFieldName = "REPORT_FILE";
  			}else{
  				// check the report field name for the customer, and also whether his subscription is still valid or not.
  				pstmt = con.prepareStatement("SELECT BRANDED_REPORT FROM CUSTOMER_DETAILS WHERE CUSTOMER_DETAILS_ID = ? AND SUBSCRIPTION_END_DATE >= CURRENT_DATE");
  				pstmt.setInt(1, customerId);
  				rs = pstmt.executeQuery();
  				if(rs.next()){
  					reportFieldName = rs.getString(1);
  				}else{
  					// customer's subscription has ended, no download allowed.
  					reportId = -1;
  				}
  				
  				rs.close();
  				pstmt.close();
  			}
  		}

  		PDFDownloadDetailsBean bean = null;
  		
  		if(reportId != -1){
  			bean = new PDFDownloadDetailsBean();
  	  		bean.setReportId(reportId);
  	  		bean.setReportFieldName(reportFieldName);
  		}
  		
  		return bean;
	}

    public PDFDownloadDetailsBean[] getMerlinEbookDownloadBeansByOrderId(int orderId)throws SQLException{

    	ArrayList list = new ArrayList();
    	SimpleDateFormat fmt = new SimpleDateFormat("MMMMM yyyy");

  		PreparedStatement pstmt = con.prepareStatement("SELECT A.REPORT_ID, REPORT_TITLE, DOWNLOAD_UUID, DOWNLOAD_DATE, DOWNLOAD_DATE AS EDITION FROM MSAL_REPORT_DOWNLOAD_HISTORY A,REPORT_TITLES B WHERE ORDER_ID = ? AND A.REPORT_ID = B.REPORT_TITLES_ID");
  		pstmt.setInt(1,orderId);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next()){
			PDFDownloadDetailsBean bean = new PDFDownloadDetailsBean();
			bean.setReportId(rs.getInt(1));
			bean.setReportTitle(rs.getString(2));
			bean.setDownloadUuid(rs.getString(3));
			bean.setDownloadDate(rs.getDate(4));
			/*
			if(rs.getDate(5)!= null){
				bean.setReportEdition(fmt.format(rs.getDate(5)));
			}else{
				bean.setReportEdition("");
			}
			*/
			list.add(bean);
		}

		rs.close();
		pstmt.close();

		PDFDownloadDetailsBean[] arrBeans = (PDFDownloadDetailsBean[])list.toArray(new PDFDownloadDetailsBean[0]);
		return arrBeans;
	}


	///////////// Functions related to PDF downloads by the Free Updates Customers ///////////

	/**
	 * Returns the status of the PDF reports that this user has subscribed to.
	 * 
	 * reportType - pdf or xls
	 */
	public PDFDownloadDetailsBean[] getPDFDetailsForFreeUpdatesCustomer(String reportIds, int reportType)throws SQLException{
		String reportDataField = null;
		
		switch(reportType){
			case MerlinUtilities.REPORT_TYPE_XLS :
				reportDataField = "REPORT_DATA";
				break;
			case MerlinUtilities.REPORT_TYPE_RANKINGS :
				reportDataField = "REPORT_RANKINGS";
				break;
			default:
				reportDataField = "REPORT_FILE"; 
		}
		
		String sql = "select REPORT_TITLES_ID,REPORT_TITLE, OCTET_LENGTH(" + reportDataField + "),REPORT_DATE " +
					" from REPORT_TITLES LEFT OUTER JOIN REPORT_FILES " +
					" on REPORT_TITLES_ID = REPORT_ID  " +
					" where REPORT_TITLES_ID in ( " + reportIds + ") order by REPORT_TITLE";

		System.out.println(sql);

		ArrayList list = new ArrayList();
		SimpleDateFormat fmt = new SimpleDateFormat("dd MMMMM yyyy");

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);

		while(rs.next()){
			PDFDownloadDetailsBean bean = new PDFDownloadDetailsBean();
			bean.setReportId(rs.getInt(1));
			bean.setReportTitle(rs.getString(2));
			bean.setReportSize(rs.getInt(3));
			if(rs.getDate(4)!= null && bean.getReportSize() > 0){
				bean.setReportEdition(fmt.format(rs.getDate(4)));
				bean.setStatus(MerlinUtilities.REPORT_STATUS_AVAILABLE);
			}else{ // this report has not yet been generated
				bean.setReportEdition("");
				bean.setStatus(MerlinUtilities.REPORT_STATUS_IN_PROGRESS);
			}

			list.add(bean);
		}

		rs.close();
		stmt.close();

		PDFDownloadDetailsBean[] arrBeans = (PDFDownloadDetailsBean[])list.toArray(new PDFDownloadDetailsBean[0]);
		return arrBeans;
	}

	public int generateNewFreeUpdateDownloadHistoryId()throws SQLException{
		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT MAX(COALESCE(REPORT_DOWNLOAD_HISTORY_ID,0)) + 1 FROM NSD.FREE_UPDATES_REPORT_DOWNLOAD_HISTORY");
		rs.next();
		int newID = rs.getInt(1);
		rs.close();
		stmt.close();

		return newID;
	}

	public int addDownloadHistoryForFreeUpdates(int reportId,int customerId)throws SQLException{
    	int newId = generateNewFreeUpdateDownloadHistoryId();

    	PreparedStatement pstmt = con.prepareStatement("insert into FREE_UPDATES_REPORT_DOWNLOAD_HISTORY(REPORT_DOWNLOAD_HISTORY_ID,REPORT_ID,CUSTOMER_ID,DOWNLOAD_DATE) values(?,?,?,current_date)");
		pstmt.setInt(1,newId);
		pstmt.setInt(2,reportId);
		pstmt.setInt(3,customerId);
		int updateCount = pstmt.executeUpdate();

		pstmt.close();

		return updateCount;
	}

	public PDFDownloadDetailsBean[] getDownloadHistoryForFreeUpdates(int customerId)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("select REPORT_TITLE,DOWNLOAD_DATE from REPORT_TITLES,FREE_UPDATES_REPORT_DOWNLOAD_HISTORY where REPORT_TITLES_ID = REPORT_ID and CUSTOMER_ID = ? order by DOWNLOAD_DATE desc");
		pstmt.setInt(1,customerId);
		ResultSet rs = pstmt.executeQuery();

		ArrayList list = new ArrayList();

		while(rs.next()){
			PDFDownloadDetailsBean bean = new PDFDownloadDetailsBean();
			bean.setReportTitle(rs.getString(1));
			bean.setDownloadDate(rs.getDate(2));

			list.add(bean);
		}

		rs.close();
		pstmt.close();

		PDFDownloadDetailsBean[] arrBeans = (PDFDownloadDetailsBean[])list.toArray(new PDFDownloadDetailsBean[0]);
		return arrBeans;

	}
	
	public CustomerDetailsBean getCustomerDetailsByEbookDownloadOrderId(int orderId)throws SQLException{
		
		CustomerDetailsBean bean = null;
		
		String sql = "select CUSTOMER_DETAILS_ID, BRANDED_REPORT, SPREADSHEET_DATA, RANKINGS_DATA from CUSTOMER_DETAILS , WEBSITE_ORDERS " +
				"WHERE CUSTOMER_DETAILS_ID = WO_CUSTOMER_DETAILS_ID and WEBSITE_ORDERS_ID = ?";
		System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, orderId);
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			bean = new CustomerDetailsBean();
			bean.setCustomerDetailsId(rs.getInt("CUSTOMER_DETAILS_ID"));
			bean.setBrandedReport(rs.getString("BRANDED_REPORT"));
			bean.setSpreadsheetData(rs.getString("SPREADSHEET_DATA"));
			bean.setRankingsData(rs.getString("RANKINGS_DATA"));
		}
		rs.close();
		pstmt.close();
		return bean;
		
	}

}